﻿using System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
//using System.Data.OracleClient;
using CrystalDecisions.Web;
using CrystalDecisions.Shared;
using CrystalDecisions.CrystalReports.Engine;
using System.Xml;
using System.IO;
using System.Globalization;



public partial class report : System.Web.UI.Page
{
    private int productionSEQ = Convert.ToInt32(ConfigurationManager.AppSettings["productionSEQ"]);

    private string site = "";
    private string xPathSite = "";

    protected DateTime dtFrom, dtTo;
    protected string strSQL = "";
    
    protected ReportInfo rptInfo=new ReportInfo();
    private DataSet dsBranch=null;

    private string lang = "zh";
    private string strAttachment = "";
    private const string SHORTDATEFORMAT = "MM/dd/yyyy";


    private ReportDocument rptDocument = new ReportDocument();
    
    
    protected void Page_Load(object sender, EventArgs e)
    {

        DataGateway dg = new DataGateway();

        xPathSite = string.Format("(not(@site) or contains(@site,'{0:s}'))", site);

        DataSet ds = null;

        if (!Page.IsPostBack)
        {
            t_efffrom.Value = String.Format("{0:MM/dd/yyyy}", DateTime.Now.Date);
            t_effto.Value = String.Format("{0:MM/dd/yyyy}", DateTime.Now.Date);

            XmlDataSourceReport.DataFile = Server.MapPath(@"reports/report_list.xml");
            XmlDataSourceReport.XPath = string.Format("/setting/report[{0:s}]", xPathSite);
            XmlDataSourceReport.DataBind();
            ddl_report.DataBind();

            //dsBranch = dg.GetBranch();
            //ViewState["branch"] = dsBranch;

            //ControlBasic cb = new ControlBasic();
            //cb.SetControlList(dsBranch.Tables[0].DefaultView, ddl_pub, "");

            ddl_pub_SelectedIndexChanged(null, null);



        }
        else
        {
            if ( ViewState["dataset"] != null ) 
                ds = (DataSet)ViewState["dataset"];

            if ( ViewState["reportinfo"] !=null ) 
                rptInfo.setInfo( ViewState["reportinfo"].ToString() );

            if (ds != null)
                bindReport(ds, rptInfo.report,false);

            if (ViewState["branch"] != null)
                dsBranch = (DataSet)ViewState["branch"];
        }

        
    }

    protected void Page_Unload(object sender, EventArgs e)
    {
        rptDocument.Close();
    }

   

    private void getSql_commission(string sqlFormat)
    {
        string strsqlcond;

        strsqlcond = string.Format(" ( pay_date between date '{0:s}' and date '{1:s}' )  ", dtFrom.ToString("yyyy-MM-dd"), dtTo.ToString("yyyy-MM-dd"));

        if (tb_agent_to.Text != "")
            tb_agent_to.Text = tb_agent.Text;

        if (tb_agent.Text != "")
            strsqlcond = strsqlcond + string.Format(" and agentcode between '{0:s}' and '{1:s}' ", tb_agent.Text, tb_agent_to.Text);

        strSQL = string.Format(sqlFormat, strsqlcond);
    }

    

    protected void btn_preview_Click(object sender, EventArgs e)
    {
        strSQL = "";
        string strTable = ddl_report.SelectedValue;
        getReportXml( strTable );
        
        if ( rptInfo.table == "")
        {
            return;
        }

        CultureInfo culture = new CultureInfo("en-US");
        culture.DateTimeFormat.ShortDatePattern = "MM/dd/yyyy";
        

        // update data from UI
        dtFrom = Convert.ToDateTime(t_efffrom.Value, culture);
        dtTo = Convert.ToDateTime(t_effto.Value, culture);

        switch ( strTable ) 
        {
            case "vr_report_comm":
            case "vr_report_comm_pub": 
                getSql_commission(rptInfo.sql);
                break;

            default:
                break;
        }
        //
        if (strSQL == "")
            return;

        //AppendSiteSQL();

        DataGateway dg = new DataGateway();
        DataSet ds = dg.GetReportDS(strSQL, rptInfo.table);
        ViewState["dataset"] = ds;

        bindReport(ds, rptInfo.report,true);
    }

    private void AppendSiteSQL()
    {
        if (site != "")
        {
            string[] splits = strSQL.Split(new string[] {"where"}, 2, StringSplitOptions.None);
            strSQL = splits[0] + string.Format("where site='{0:s}' and", site) + splits[1];
        }
    }

    private void ExportToDisk(string fileName)
    {
        ExportOptions exportOpts = new ExportOptions();
        DiskFileDestinationOptions diskOpts =  ExportOptions.CreateDiskFileDestinationOptions();

        exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat;
        exportOpts.ExportDestinationType = ExportDestinationType.DiskFile;

        
        ltAddress.Text = String.Format("<a href=\"./export/{0}\" target=\"_blank\">{1}</a>", fileName, fileName);

        fileName = this.Context.Request.PhysicalApplicationPath + "/export/" + fileName;

        strAttachment = fileName;

        diskOpts.DiskFileName = fileName;
        exportOpts.ExportDestinationOptions = diskOpts;

        rptDocument.Export(exportOpts);
    }


    protected void bindReport(DataSet ds, string reportFile,bool bExport)
    {
        //crystal report push module

        rptDocument.Load(Server.MapPath(reportFile), CrystalDecisions.Shared.OpenReportMethod.OpenReportByTempCopy);
        rptDocument.SetDataSource(ds);

        rptDocument.SetParameterValue("StartAgentCode", tb_agent.Text);
        rptDocument.SetParameterValue("EndAgentCode", tb_agent_to.Text);

        rptDocument.SetParameterValue("StartDate", t_efffrom.Value);
        rptDocument.SetParameterValue("EndDate", t_effto.Value);


        if (bExport)
        {
            string fileName = Path.GetFileNameWithoutExtension(Server.MapPath(reportFile));
            ExportToDisk(fileName + ".pdf");
        }

        CRViewer.ReportSource = rptDocument;
    }


    protected void getReportXml(string value)
    {

        xPathSite = string.Format("(not(@site) or contains(@site,'{0:s}'))", ddl_pub.SelectedValue);

        DataSet ds2 = new DataSet();

        XmlNode node;
        XmlDocument xmldoc = new XmlDocument();
        xmldoc.Load(Server.MapPath(@"reports/report_list.xml"));

        XmlElement root = xmldoc.DocumentElement;
        node = root.SelectSingleNode(String.Format("/setting/report[@value=\"{0:s}\" and {1:s}]", value, xPathSite));

        XmlNode XChild = node.SelectSingleNode("crystalname");
        rptInfo.report = XChild.InnerText;
        XChild = node.SelectSingleNode("sql");
        rptInfo.sql = XChild.InnerText;

        
        rptInfo.table = value;
        XChild = node.SelectSingleNode("table");
        if ( XChild != null ) 
            rptInfo.table = XChild.InnerText;


        ViewState["reportinfo"] = rptInfo.getInfo();
    }

    protected void ddl_pub_SelectedIndexChanged(object sender, EventArgs e)
    {
        site = ddl_pub.SelectedValue;
        xPathSite = string.Format("(not(@site) or contains(@site,'{0:s}'))", site);

        XmlDataSourceReport.DataFile = Server.MapPath(@"reports/report_list.xml");
        XmlDataSourceReport.XPath = string.Format("/setting/report[{0:s}]", xPathSite);
        XmlDataSourceReport.DataBind();
        ddl_report.DataBind();

        ddl_report_SelectedIndexChanged(null, null);
    }
    protected void ddl_report_SelectedIndexChanged(object sender, EventArgs e)
    {
        DateTime dtFirstDate  = new DateTime(DateTime.Now.Year, DateTime.Now.Month,1);
        dtFirstDate = dtFirstDate.AddMonths(-1);
        DateTime dtLastDate = dtFirstDate.AddMonths(1).AddDays(-1);

        ltAddress.Text = "";
        CRViewer.ReportSource = null;


        switch ( ddl_report.SelectedValue ) 
        {
            case "vrpt_payment":
                t_efffrom.Value = DateTime.Now.AddDays(-1).ToString(SHORTDATEFORMAT);
                t_effto.Value = DateTime.Now.AddDays(-1).ToString(SHORTDATEFORMAT);
                break;

            case "vr_report_comm":
            case "vr_report_comm_pub":
                t_efffrom.Value = dtFirstDate.ToString(SHORTDATEFORMAT);
                t_effto.Value = dtLastDate.ToString(SHORTDATEFORMAT);
                break;

            default:
                break;

        }
    }


    // email invoice to agent

    private string SendEmail(string subject, string body, string emailto,string filename)
    {
        return "";
        //if (emailto == null || emailto == "")
        //    return "";

        //filename = this.Context.Request.PhysicalApplicationPath + "/export/" + filename;

        //string result = "";
        //qqMail.qqSmtp sendmail = new qqMail.qqSmtp();
        //sendmail.CharSet = "UTF-8";

        //sendmail.Subject = subject;
        //sendmail.From = ConfigurationManager.AppSettings["email_from"];
        //// 
        //if (ConfigurationManager.AppSettings["email_to"] != null)
        //{   // debug config 
        //    sendmail.To = ConfigurationManager.AppSettings["email_to"];
        //}
        //else
        //    sendmail.To = emailto;

        //sendmail.HtmlBody = body;

        //if (ConfigurationManager.AppSettings["email_user"] != null)
        //{
        //    sendmail.SmtpUser = ConfigurationManager.AppSettings["email_user"];
        //    sendmail.SmtpPass = ConfigurationManager.AppSettings["email_pass"];
        //    sendmail.UseDirectSend = false;
        //}
        //else
        //    sendmail.UseDirectSend = true;
        //sendmail.SmtpServer = ConfigurationManager.AppSettings["email_smtp"];
        //sendmail.MailFormat = qqMail.qqSmtp.EnumMailFormat.mime;
        //sendmail.AddAttachment(filename);
        

        //try
        //{
        //    sendmail.SendMail();

        //    if (sendmail.isOk)
        //    {
        //        result = "1";
        //    }
        //    else
        //    {
        //        result = sendmail.ErrDes;
        //    }
        //}
        //catch
        //{
        //    result = "Failed to send email";
        //}
        //sendmail = null;
        //return result;
    }


    private void getSql_invoice_agent(string sqlFormat,string agent_id,string customer)
    {
        string strDateFilter = "";

        // LA case
        if (productionSEQ != 51)
        {
            strDateFilter = string.Format(" and ( order_date between date '{0}' and date '{1}' or trunc(cdate) between date '{0}' and date '{1}' )", dtFrom.ToString("yyyy-MM-dd"), dtTo.ToString("yyyy-MM-dd"));
        }

        strSQL = string.Format(sqlFormat, String.Format(" agent_id = '{0}' and company_code = '{1}' ", agent_id, customer) + strDateFilter);
    }

    private ArrayList GetAgentList(DataSet ds)
    {
        ArrayList agentList = new ArrayList();
        DataRow row = null;
        string agentid = "";

        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
        {
            row = ds.Tables[0].Rows[i];

            agentid = String.Format("{0},{1},{2}", row["agent_id"].ToString(), row["email"].ToString(), row["company_code"].ToString());

            if ( agentList.IndexOf(agentid) < 0 ) 
            {
                agentList.Add(agentid);
            }
        }
        return agentList;
    }

   
    


    private void ExportToMail(string fileName,ReportDocument rptDoc)
    {
        ExportOptions exportOpts = new ExportOptions();
        DiskFileDestinationOptions diskOpts = ExportOptions.CreateDiskFileDestinationOptions();

        exportOpts.ExportFormatType = ExportFormatType.PortableDocFormat;
        exportOpts.ExportDestinationType = ExportDestinationType.DiskFile;    

        fileName = this.Context.Request.PhysicalApplicationPath + "/export/" + fileName;

        diskOpts.DiskFileName = fileName;
        exportOpts.ExportDestinationOptions = diskOpts;

        rptDoc.Export(exportOpts);
    }

}

public class ReportInfo
{
    public string table;
    public string report;
    public string sql;
    public string getInfo()
    {
        return table + "," + report + "," + sql;
    }
    public void setInfo(string strinfo)
    {
        string[] info = strinfo.Split(',');
        table = info[0];
        report = info[1];
        sql = info[2];
    }
    public ReportInfo()
    {
        table = "";
        report = "";
        sql = "";
    }
};